﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using GBH.Models;
using GBH.Models.Dao;
using Newtonsoft.Json;
using System.Net.Http.Formatting;
using Newtonsoft.Json.Serialization;
using OfficeOpenXml;
using System.IO;

namespace GBH.Controllers
{
    public class DaySellMinMaxController : ApiController
    {
        // GET api/daysellminmax
        public HttpResponseMessage PostGetAllDaySellMinMax()
        {
            using (var db = new GBHEntities())
            {
                var daysell = (from minmax in db.daysellMinMaxes
                              join branch in db.Branches on minmax.location_id equals branch.mprovtrans
                              join Mat in db.materials on minmax.product_id equals Mat.npibarcode
                              //where minmax.isEnable
                              select new
                              {
                                  branchName = branch.mprovdesc,
                                  isEnable = minmax.isEnable,
                                  location_id = minmax.location_id,
                                  matName = Mat.mnamethai,
                                  max = minmax.max,
                                  min = minmax.min,
                                  product_id = minmax.product_id,
                                  rop = minmax.rop,
                                  updateDate = minmax.updateDate
                              }).ToList();
                var formatter = new JsonMediaTypeFormatter();
                  var json =formatter.SerializerSettings;

                  json.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;
                  json.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Utc;
                  json.NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore;
                  json.Formatting = Newtonsoft.Json.Formatting.Indented;
                  json.ContractResolver = new CamelCasePropertyNamesContractResolver();
                  return Request.CreateResponse(HttpStatusCode.OK, daysell, formatter);

            }
        }

        public HttpResponseMessage GetExportAllDaySellMinMax()
        {
            using (var db = new GBHEntities())
            {
                var daysell = (from minmax in db.daysellMinMaxes
                               join branch in db.Branches on minmax.location_id equals branch.mprovtrans
                               join Mat in db.materials on minmax.product_id equals Mat.npibarcode
                               //where minmax.isEnable
                               select new
                               {
                                   product_id = minmax.product_id,
                                   matName = Mat.mnamethai,
                                   location_id = minmax.location_id,
                                   branchName = branch.mprovdesc,
                                   min = minmax.min,
                                   max = minmax.max,
                                   rop = minmax.rop,
                                   isEnable = minmax.isEnable,
                               });

                var dt = new DataTable();
                dt.Columns.Add("รหัสสินค้า");
                dt.Columns.Add("ชื่อสินค้า");
                dt.Columns.Add("Branch");
                dt.Columns.Add("ชื่อ Branch");
                dt.Columns.Add("Min");
                dt.Columns.Add("Max");
                dt.Columns.Add("ROP");
                dt.Columns.Add("Active");
                foreach (var sell in daysell)
                {
                    dt.Rows.Add(sell.product_id,sell.matName, sell.location_id,sell.branchName, sell.min, sell.max, sell.rop, sell.isEnable);
                }
               

                using (ExcelPackage pck = new ExcelPackage())
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("PI");
                    ws.Cells["A1"].LoadFromDataTable(dt, true);
                    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK);
                    response.Content = new StreamContent(new MemoryStream(pck.GetAsByteArray()));
                    response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                    response.Content.Headers.Add("content-disposition", "attachment;  filename=daySellMinMax.xlsx" );
                    return response;
                }
            }
            
        }

        public bool PostNewMinMax(daysellMinMax newMinMax)
        {
            if (newMinMax != null)
            {
                var daysellMinMaxProvider = new DaysellMinMaxDao();
                var result = daysellMinMaxProvider.InsertSellMinMax(newMinMax);
                return result;
            }
            return false;
        }

        public bool PostDeleteMinMax(daysellMinMax newMinMax)
        {
            if (newMinMax != null)
            {
                var daysellMinMaxProvider = new DaysellMinMaxDao();
                var result = daysellMinMaxProvider.DeleteSellMinMax(newMinMax);
                return result;
            }
            return false;
        }

        // PUT api/daysellminmax/5
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE api/daysellminmax/5
        public void Delete(int id)
        {
        }
    }
}
